package es.xpt.ysf.commons.jaas;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.TreeSet;

import es.xpt.ysf.dao.Connection;
import es.xpt.ysf.dao.DAOErrors;
import es.xpt.ysf.dao.DAOException;
import es.xpt.ysf.dao.DAOUtils;
import es.xpt.ysf.dao.DbDAO;
import es.xpt.ysf.dao.PreparedStatement;
import es.xpt.ysf.dao.Statement;
import es.xpt.ysf.jaas.YSFLoginModule;
import es.xpt.ysf.jaas.YSFPrincipal;
import es.xpt.ysf.model.Item;

public class RolesDAO extends DbDAO<Item> {

	public RolesDAO(Connection c, YSFPrincipal principal, DAOErrors errors) {
		super(c, principal, errors);
	}


	@Override
	protected Item populateBean(ResultSet rs) throws SQLException {

		return DAOUtils.populateItemNames(rs);
	}

	public List<Item> getRoles() throws DAOException {
		String selectStatement = "SELECT id item_id, name item_shortName, description item_longName" 
				+ " FROM ysf_jaas_role"
				+ " ORDER BY name";
		return listQuery(selectStatement);
	}

	public List<Item> getRolesByBusinessArea(String businessArea) throws DAOException {
		String selectStatement = "SELECT id item_id, name item_shortName, description item_longName" 
				+ " FROM ysf_jaas_role ROL"
				+ " INNER JOIN ysf_cb_issues_subscriber SUB"
				+ " ON ROL.id = SUB.role";
		
		if (businessArea != null && !businessArea.isEmpty()) {
			selectStatement += " WHERE SUB.businessArea = '" + businessArea + "'";
		}
		selectStatement +=  " ORDER BY name";
		return listQuery(selectStatement);
	}

	public List<Item> getUserRoles(String principal, String credential)
			throws DAOException {
		String selectStatement = "SELECT r.id item_id, r.name item_shortName, r.description item_longName "
				+ " FROM ysf_jaas_role r inner join ysf_jaas_principal_role "
				+ " on r.id=role inner join ysf_jaas_principal p on principal=p.id"
				+ " WHERE principal='" + principal + "'";
		if ( credential!=null )
			selectStatement += " and password='" + YSFLoginModule.encrypt(credential) + "'";
		return listQuery(selectStatement);
	}

	public void deleteRole(String role)
			throws DAOException {
			String deletePrivileges = "delete from ysf_jaas_role_privilege where role='" + role + "'";
			String deleteRoleUsers = "delete from ysf_jaas_principal_role where role='" + role + "'";
			String deleteRole = "delete from ysf_jaas_role where id='" + role + "'";
			
			Statement stmt = null;
			try {
				stmt = con.createStatement();
				stmt.executeUpdate(deletePrivileges);
				stmt.executeUpdate(deleteRoleUsers);
				stmt.executeUpdate(deleteRole);
			} catch (SQLException se) {
				throw new DAOException(se);
			} finally {
				if (stmt != null)
					stmt.close();
			}
	}

	public void insertRole(Item role)
			throws DAOException {
		String statement = "insert into ysf_jaas_role (id, name, description) values (?,?,?)";
		
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);

			stmt.setString(1, role.getCode());
			stmt.setString(2, role.getShortName());
			stmt.setString(3, role.getLongName());
			stmt.executeUpdate();
		} catch (SQLException se) {
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void updateRole(Item role)
			throws DAOException {
		String statement = "update ysf_jaas_role set name=?, description=?"
				+ " where id = ?";
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			stmt.setString(1, role.getShortName());
			stmt.setString(2, role.getLongName());
			stmt.setString(3, role.getCode());
			stmt.executeUpdate();
		} catch (SQLException se) {
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public TreeSet<String> getRolePrivileges(String role)
			throws DAOException {
		
		String selectStatement = "SELECT privilege FROM ysf_jaas_role_privilege "
		+ " WHERE role='" + role + "' ORDER BY privilege";
		
		Statement stmt = null;
	    try {
	    	stmt = con.createStatement();
	    	ResultSet rs = stmt.executeQuery(selectStatement);
	    	TreeSet<String> ts = new TreeSet<String>();
	    	while ( rs.next() ) {
	    		ts.add(rs.getString(1));
	    	}
	       return ts;
	    } catch (SQLException se){
	      throw new DAOException(se);
	    } finally {
	    	if ( stmt!=null ) stmt.close();
	    }
	}

	public void removePrivilege(String role, String privilege)
			throws DAOException {
		String deleteStatement = "delete from ysf_jaas_role_privilege"
			 + " where role='" + role + "' and privilege='" + privilege + "'";
		
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(deleteStatement);
		} catch (SQLException se) {
			throw new DAOException(se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void removePrivileges(String role, String[] privileges)
			throws DAOException {
		
		String deleteStatement = "delete from ysf_jaas_role_privilege"
			 + " where role='" + role + "' and privilege=?";
		
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(deleteStatement);
			for (String s: privileges) {
				stmt.setString(1, s);
				stmt.addBatch();
			}
			stmt.executeBatch();
		} catch (SQLException se) {
			throw new DAOException(se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void insertPrivileges(String role, String[] privileges)
			throws DAOException {
		
		String insertStatement = "insert into ysf_jaas_role_privilege (role, privilege) " 
				  + " values ('" + role + "', ?)";
		
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(insertStatement);
			for (String s: privileges) {
				stmt.setString(1, s);
				stmt.addBatch();
			}
			stmt.executeBatch();
		} catch (SQLException se) {
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void insertPrivilege(String role, String privilege)
			throws DAOException {
		String insertStatement = "insert into ysf_jaas_role_privilege (role, privilege) " 
			  + " values ('" + role + "', '" + privilege + "')";
		
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(insertStatement);
		} catch (SQLException se) {
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

}
